﻿using MetroDataApi.Models;
using Newtonsoft.Json.Linq;
using ReadPointsRingNumberExcel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace MetroDataApi.Controllers.Management
{
    public class MonitorPointsConfigController : ApiController
    {


        // POST api/monitorpointsconfig
        public bool Post([FromBody]string value)
        {
            try
            {
                JObject jsonData = JObject.Parse(value);
                var url = jsonData["url"].ToString();
                var fileName = jsonData["fileName"].ToString();
                int prjId = jsonData["prjId"].ToObject<int>();
                using (var db = new MetroDataDataContext())
                using (WebClient Client = new WebClient())
                {
                    var localPath = System.Web.HttpContext.Current.Server.MapPath(@"~/Excels");
                    localPath += @"\" + fileName;
                    Client.DownloadFile(url, localPath);
                    ReadExcel read = new ReadExcel(localPath);
                    var list = read.ReadFile();
                    var exsited = from p in db.MonitorPointsDictionary
                                  where p.PrjId == prjId
                                  select p;
                    db.MonitorPointsDictionary.DeleteAllOnSubmit(exsited);

                    var newList = from l in list
                                  select new MonitorPointsDictionary()
                                  {
                                      PrjId = prjId,
                                      MonitorType = l.RingNumber,//懒得再写了,刚好第二列就是ringnumber,第三列就是mileage
                                      Location = l.Mile,
                                      Name = l.Name
                                  };

                    db.ExecuteCommand(@"update MonitorPointsDictionary set 
	                                    MonitorPointsDictionary.OriginValue=uu.Value,
	                                    MonitorPointsDictionary.OriginDate =uu.DateTime
	                                    from 
	                                    (select MonitorPoints.Name,MonitorPoints.Value,MonitorPoints.DateTime from MonitorPoints, 
	                                    (select Name,Min(datetime) as DateTime from 
	                                    (select tb1.Name,tb2.DateTime,tb2.Value from (select Name from MonitorPoints where PrjId=@p0 group by Name) as tb1
	                                    left join 
	                                    (select MonitorPoints.Name,MonitorPoints.Value,MonitorPoints.DateTime from MonitorPoints where MonitorPoints.PrjId=@p0) as tb2 
	                                    on (tb1.Name = tb2.Name) group by tb1.Name,tb2.DateTime,tb2.Value ) as tb
	                                    group by tb.Name) as mx
	                                    where MonitorPoints.PrjId=@p0 and MonitorPoints.Name=mx.Name and MonitorPoints.DateTime=mx.DateTime) as uu
	                                    where MonitorPointsDictionary.PrjId=@p0 and MonitorPointsDictionary.Name=uu.Name", prjId);

                    db.MonitorPointsDictionary.InsertAllOnSubmit(newList);
                    db.SubmitChanges();
                }
                return true;
            }
            catch
            {
                return false;
            }

        }


    }
}
